﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using Backend.DTO;
using System.Data;

namespace Backend.DAL
{
    class UserDAL
    {
        private SqlConnection sqlConnection;

        public UserDAL()
        {
            DbConn aConn = new DbConn();
            sqlConnection = aConn.GetUssdSqlConnection;
        }

        public List<UserDTO> GetAllUser(string userType = "", int excludeUserId = 0)
        {
            string whereClause = string.Empty;
            List<UserDTO> listOfUsers = new List<UserDTO>();

            try
            {
                sqlConnection.Open();

                if (!string.IsNullOrEmpty(userType))
                {
                    whereClause += " AND UserType = '" + userType + "' "; 
                }

                if (excludeUserId > 0)
                {
                    whereClause += " AND Id != " + excludeUserId + " ";
                }

                SqlCommand command = new SqlCommand("SELECT * FROM Users WHERE IsActive = 1" + whereClause, sqlConnection);
                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        UserDTO userDTO = new UserDTO();

                        userDTO.Id = (int)reader["Id"];
                        userDTO.LoginId = reader["LoginId"].ToString();
                        userDTO.Password = reader["Password"].ToString();
                        userDTO.FirstName = reader["FirstName"].ToString();
                        userDTO.LastName = reader["LastName"].ToString();
                        userDTO.DateOfBirth = Convert.ToDateTime(reader["DateOfBirth"]);
                        userDTO.Gender = reader["Gender"].ToString();
                        userDTO.Email = reader["Email"].ToString();
                        userDTO.UserType = reader["UserType"].ToString();
                        userDTO.RegisteredDate = Convert.ToDateTime(reader["RegisteredDate"].ToString());
                        userDTO.IsActive = Convert.ToBoolean(reader["IsActive"]);
                        userDTO.ResetPasswordKey = reader["ResetPasswordKey"].ToString();

                        listOfUsers.Add(userDTO);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return listOfUsers;
        }

        public UserDTO GetUser(string loginId)
        {
            UserDTO userDTO = null;

            try
            {
                sqlConnection.Open();

                try
                {
                    SqlCommand command = new SqlCommand("SELECT * FROM Users WHERE LoginId = @login", sqlConnection);
                    command.Parameters.AddWithValue("login", loginId);

                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            userDTO = new UserDTO();

                            userDTO.Id = (int)reader["Id"];
                            userDTO.LoginId = reader["LoginId"].ToString();
                            userDTO.Password = reader["Password"].ToString();
                            userDTO.FirstName = reader["FirstName"].ToString();
                            userDTO.LastName = reader["LastName"].ToString();
                            userDTO.DateOfBirth = Convert.ToDateTime(reader["DateOfBirth"]);
                            userDTO.Gender = reader["Gender"].ToString();
                            userDTO.Email = reader["Email"].ToString();
                            userDTO.UserType = reader["UserType"].ToString();
                            userDTO.RegisteredDate = Convert.ToDateTime(reader["RegisteredDate"].ToString());
                            userDTO.IsActive = Convert.ToBoolean(reader["IsActive"]);
                            userDTO.ResetPasswordKey = reader["ResetPasswordKey"].ToString();

                            break;
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return userDTO;
        }

        public UserDTO GetUserById(int id)
        {
            UserDTO userDTO = null;

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT * FROM Users WHERE Id = @userId", sqlConnection);
                command.Parameters.AddWithValue("userId", id);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        userDTO = new UserDTO();

                        userDTO.Id = (int)reader["Id"];
                        userDTO.LoginId = reader["LoginId"].ToString();
                        userDTO.Password = reader["Password"].ToString();
                        userDTO.FirstName = reader["FirstName"].ToString();
                        userDTO.LastName = reader["LastName"].ToString();
                        userDTO.DateOfBirth = Convert.ToDateTime(reader["DateOfBirth"]);
                        userDTO.Gender = reader["Gender"].ToString();
                        userDTO.Email = reader["Email"].ToString();
                        userDTO.UserType = reader["UserType"].ToString();
                        userDTO.RegisteredDate = Convert.ToDateTime(reader["RegisteredDate"].ToString());
                        userDTO.IsActive = Convert.ToBoolean(reader["IsActive"]);
                        userDTO.ResetPasswordKey = reader["ResetPasswordKey"].ToString();

                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return userDTO;
        }

        public UserDTO GetUserByEmail(string email)
        {
            UserDTO userDTO = null;

            try
            {
                sqlConnection.Open();

                try
                {
                    SqlCommand command = new SqlCommand("SELECT * FROM Users WHERE Email = @Email", sqlConnection);
                    command.Parameters.AddWithValue("Email", email);

                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            userDTO = new UserDTO();

                            userDTO.Id = (int)reader["Id"];
                            userDTO.LoginId = reader["LoginId"].ToString();
                            userDTO.Password = reader["Password"].ToString();
                            userDTO.FirstName = reader["FirstName"].ToString();
                            userDTO.LastName = reader["LastName"].ToString();
                            userDTO.DateOfBirth = Convert.ToDateTime(reader["DateOfBirth"]);
                            userDTO.Gender = reader["Gender"].ToString();
                            userDTO.Email = reader["Email"].ToString();
                            userDTO.UserType = reader["UserType"].ToString();
                            userDTO.RegisteredDate = Convert.ToDateTime(reader["RegisteredDate"].ToString());
                            userDTO.IsActive = Convert.ToBoolean(reader["IsActive"]);
                            userDTO.ResetPasswordKey = reader["ResetPasswordKey"].ToString();

                            break;
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return userDTO;
        }

        public UserDTO GetUserByResetKey(string key)
        {
            UserDTO userDTO = null;

            try
            {
                sqlConnection.Open();

                try
                {
                    SqlCommand command = new SqlCommand("SELECT * FROM Users WHERE ResetPasswordKey = @resetKey", sqlConnection);
                    command.Parameters.AddWithValue("resetKey", key);

                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            userDTO = new UserDTO();

                            userDTO.Id = (int)reader["Id"];
                            userDTO.LoginId = reader["LoginId"].ToString();
                            userDTO.Password = reader["Password"].ToString();
                            userDTO.FirstName = reader["FirstName"].ToString();
                            userDTO.LastName = reader["LastName"].ToString();
                            userDTO.DateOfBirth = Convert.ToDateTime(reader["DateOfBirth"]);
                            userDTO.Gender = reader["Gender"].ToString();
                            userDTO.Email = reader["Email"].ToString();
                            userDTO.UserType = reader["UserType"].ToString();
                            userDTO.RegisteredDate = Convert.ToDateTime(reader["RegisteredDate"].ToString());
                            userDTO.IsActive = Convert.ToBoolean(reader["IsActive"]);
                            userDTO.ResetPasswordKey = reader["ResetPasswordKey"].ToString();

                            break;
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return userDTO;
        }

        public string AddUser(UserDTO aUser)
        {
            int insertId = 0;

            try
            {
                sqlConnection.Open();

                string sql = "INSERT INTO Users(LoginId, Password, FirstName, LastName, DateOfBirth, Gender, Email, UserType, RegisteredDate, IsActive) " +
                                "VALUES(@loginId, @password, @firstName, @lastName, @dateOfBirth, @gender, @email, @userType, @registeredDate, @isActive)";
                SqlCommand command = new SqlCommand(sql, sqlConnection);

                command.Parameters.AddWithValue("loginId", aUser.LoginId);
                command.Parameters.AddWithValue("password", aUser.Password);
                command.Parameters.AddWithValue("firstName", aUser.FirstName);
                command.Parameters.AddWithValue("lastName", aUser.LastName);
                command.Parameters.AddWithValue("dateOfBirth", aUser.DateOfBirth);
                command.Parameters.AddWithValue("gender", aUser.Gender);
                command.Parameters.AddWithValue("email", aUser.Email);
                command.Parameters.AddWithValue("userType", aUser.UserType);
                command.Parameters.AddWithValue("registeredDate", aUser.RegisteredDate);
                command.Parameters.AddWithValue("isActive", aUser.IsActive);

                insertId = command.ExecuteNonQuery();

                if (insertId > 0)
                {
                    return "success";
                }
                else
                {
                    return "Unknown error occourd.";
                }
            }
            catch (SqlException sqlException)
            {
                throw sqlException;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        public bool UpdateUser(string field, string value, int id)
        {
            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("UPDATE Users SET " + field + " = @resetPasswordKey WHERE Id=@userId", sqlConnection);
                command.Parameters.AddWithValue("resetPasswordKey", value);
                command.Parameters.AddWithValue("userId", id);

                command.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        public void DeleteUser(int deleteId)
        {
            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("DELETE FROM Users WHERE Id = @id", sqlConnection);
                command.Parameters.AddWithValue("id", deleteId);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        public bool IsEmailExist(string emailAddress)
        {
            try
            {
                sqlConnection.Open();

                string sql = "SELECT * FROM Users where Email=@email";

                SqlCommand command = new SqlCommand(sql, sqlConnection);
                command.Parameters.AddWithValue("email", emailAddress);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException sqlException)
            {
                throw sqlException;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                sqlConnection.Close();
            }
        }

        public bool IsLoginIdExist(string login)
        {
            try
            {
                sqlConnection.Open();

                string sql = "SELECT * FROM Users where LoginId=@loginId";

                SqlCommand command = new SqlCommand(sql, sqlConnection);
                command.Parameters.AddWithValue("loginId", login);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException sqlException)
            {
                throw sqlException;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                sqlConnection.Close();
            }
        }
    }
}
